package czy.demo.service;


import czy.demo.pojo.Person;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

/**
 * 使用命名参数，参数以Map形式传递，sql中以:参数名形式标识参数
 */
@Service
public class NamedParameterJdbcTemplateService {

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;

    /* 从结果集中获取Person对象 */
    public Person getOneFromResultSet(ResultSet rs)throws SQLException, DataAccessException {
        Person person = new Person();
        person.setId(rs.getInt("id"));
        person.setName(rs.getString("name"));
        person.setAge(rs.getInt("age"));
        /* Timestamp为Date的子类 */
        person.setCreateTime(rs.getTimestamp("create_time"));
        return person;
    }

    /* ResultSetExtractor映射结果集 */
    public Person getOneByIdWithResultSetExtractor(Integer id){
        Map<String,Object> params = new HashMap<>();
        params.put("id",id);
        /* 可以使用预编译sql语句，即在sql中使用？代替参数，将参数以数组形式传递 */
        return this.jdbcTemplate.query("select * from person where id=:id",params, new ResultSetExtractor<Person>() {
            @Override
            public Person extractData(ResultSet rs) throws SQLException, DataAccessException {
                rs.next();
                return getOneFromResultSet(rs);
            }
        });
    }

}
